Amazon Redshift: 分散ノード環境下でのIDENTITY機能の挙動について

小ネタといいますか、ワークアラウンド的な備忘録として書き留めておいたほうが良いかなと思った事案がありましたので当エントリにその内容を記しておきたいと思います。

Amazon Redshiftには、CREATE TABLE文作成の際に所定の項目にIDENTITYというオプションを指定する事で、指定の項目(INT型もしくはBIGINT型)を採番数値として活用出来る、というのは以下のエントリでもご紹介していました。

• Amazon Redshift: テーブル作成時のIDENTITYオプションによる自動採番処理を行う ｜ Developers.IO
• CREATE TABLE - Amazon Redshift

遭遇した状況

で、とある局面でこのIDENTITYを使って採番処理を行わせよう、と思い処理を実施。定義はIDENTITY(1,1)、としました。1から始めて1ずつ増分していく、というものです。

# CREATE TABLE public.identity_test (
  itemname1 VARCHAR(20) NOT NULL,
  itemname2 VARCHAR(20) NOT NULL,
  nums INT,
  id INT IDENTITY(1,1) NOT NULL );
 
# INSERT INTO public.identity_test ( itemname1, itemname2, nums )
SELECT ...... FROM ......;

実施後に採番後の値を確認してみたのですが、最初のうちは特に問題無く、指定した通りになっていたものの、

 itemname1 | itemname2 | nums | id 
-----------+-----------+------+----
 xxxxx     | yyyyy     |  999 |  1
 xxxxx     | yyyyy     |  999 |  2
 xxxxx     | yyyyy     |  999 |  3
 xxxxx     | yyyyy     |  999 |  4
 xxxxx     | yyyyy     |  999 |  5
 xxxxx     | yyyyy     |  999 |  6
 xxxxx     | yyyyy     |  999 |  7
 xxxxx     | yyyyy     |  999 |  8
 xxxxx     | yyyyy     |  999 |  9
 xxxxx     | yyyyy     |  999 | 10
 xxxxx     | yyyyy     |  999 | 11
 xxxxx     | yyyyy     |  999 | 12
 xxxxx     | yyyyy     |  999 | 13
 xxxxx     | yyyyy     |  999 | 14
 xxxxx     | yyyyy     |  999 | 15
 xxxxx     | yyyyy     |  999 | 16
 xxxxx     | yyyyy     |  999 | 17
 xxxxx     | yyyyy     |  999 | 18
 xxxxx     | yyyyy     |  999 | 19
 xxxxx     | yyyyy     |  999 | 20
 xxxxx     | yyyyy     |  999 | 21
 xxxxx     | yyyyy     |  999 | 22
 xxxxx     | yyyyy     |  999 | 23
 xxxxx     | yyyyy     |  999 | 24
 xxxxx     | yyyyy     |  999 | 25
 xxxxx     | yyyyy     |  999 | 26
 xxxxx     | yyyyy     |  999 | 27
 xxxxx     | yyyyy     |  999 | 28
 xxxxx     | yyyyy     |  999 | 29
 xxxxx     | yyyyy     |  999 | 30
(30 行)

次第に1ずつでは無く番号が飛び飛びになってしまっていました。(注：id以外の項目については、実際は値としては全て別の内容になっております)

 itemname1 | itemname2 | nums |    id    
-----------+-----------+------+----------
 xxxxx     | yyyyy     |  999 | 8598151
 xxxxx     | yyyyy     |  999 | 8598055
 xxxxx     | yyyyy     |  999 | 8597959
 xxxxx     | yyyyy     |  999 | 8597863
 xxxxx     | yyyyy     |  999 | 8597767
 xxxxx     | yyyyy     |  999 | 8597671
 xxxxx     | yyyyy     |  999 | 8597575
 xxxxx     | yyyyy     |  999 | 8597479
 xxxxx     | yyyyy     |  999 | 8597383
 xxxxx     | yyyyy     |  999 | 8597287
 xxxxx     | yyyyy     |  999 | 8597191
 xxxxx     | yyyyy     |  999 | 8597095
 xxxxx     | yyyyy     |  999 | 8596999
 xxxxx     | yyyyy     |  999 | 8596903
 xxxxx     | yyyyy     |  999 | 8596807
 xxxxx     | yyyyy     |  999 | 8596711
 xxxxx     | yyyyy     |  999 | 8596615
 xxxxx     | yyyyy     |  999 | 8596519
 xxxxx     | yyyyy     |  999 | 8596423
 xxxxx     | yyyyy     |  999 | 8596327
 xxxxx     | yyyyy     |  999 | 8596231
 xxxxx     | yyyyy     |  999 | 8596135
 xxxxx     | yyyyy     |  999 | 8596039
 xxxxx     | yyyyy     |  999 | 8595943
 xxxxx     | yyyyy     |  999 | 8595847
 xxxxx     | yyyyy     |  999 | 8595751
 xxxxx     | yyyyy     |  999 | 8595655
 xxxxx     | yyyyy     |  999 | 8595559
 xxxxx     | yyyyy     |  999 | 8595463
 xxxxx     | yyyyy     |  999 | 8595367
(30 行)

明らかに(1,1)ってのが無視されていますね。また、色々なケースで試してみましたが、ORDER BYで指定したソート順にすらも並んだ状態で採番されていない、という事もありました。

要因

関連する事象で幾つか調べてみたところ、以下のブログエントリが見つかりました。今回の検証ケース同様に、データのCOPYやINSERT INTO文の実行結果が意図した設定値では無い状態で採番されてしまっている、というものです。詳細な解説はここでは省きますが、どうやら分散環境である事が影響しており、必ずしも意図した形でのソート順やルール通りの採番はなされない模様。以前私が検証した時の状況としては内部検証用に立ち上げた小さいサイズのクラスタを使っていたので、その辺りも影響してルール通りにちゃんと採番されていたのかも知れません。

ノード/スライス数に併せて対応を施す策についても一部記載があるようですが、クラスタリサイズ等が頻繁に行われるような場合だと採用に踏み切るのも少し怖い気がします。

• Redshift-Identity column SEED-STEP behavior with COPY command
• Redshift - Identity column SEED-STEP with Insert statement

対策

これらの状況を受けて、取れそうな対策としては以下のようなものがあるかと思います。

• 1).INT型の数値範囲(最大値:2147483647(約21億))で足りなくなる事が懸念されるならば、 BIGINT型(最大値:9223372036854775807(約922兆))をID採番項目のデータ型として使う
• 2).採番の仕組みを手組のSQLで実現させる

1).については、想定される情報量と、実際に対象となるデータを用いて採番を行ってみて、想定する期間内における採番数値のMAXがINT型の数値範囲(約21億)を超えないようであればINT型のまま、危ないな〜と思ったらBIGINT型の数値範囲(約922兆)をデータ型として指定する、というものです。正直クラスタの構成によってどういった形で番号が"飛ぶ"かは読めない部分がありますので、実測値を元にこの辺りは判断をして頂くのがより確実となるかと思います。

もう1つの2).については、『IDENTITYを使わない』というものです。以下の様な形でROW_NUMBER()を用いたSQLを使う事で、採番用項目として定義したidの値に、所定のルールで番号を付与する事が可能です。処理パフォーマンスとしてはIDENTITYを使うものより若干スピードが落ちますが、そこまで気になる程ではありません。明確にこの採番ルールで番号が割り振られないと困る！という場合にはこちらの案をベースに検討頂く方が良いかも知れません。

• ROW_NUMBER ウィンドウ関数 - Amazon Redshift
• ROW_NUMBER ウィンドウ関数の例 - Amazon Redshift

# CREATE TABLE public.identity_test (
  itemname1 VARCHAR(20) NOT NULL,
  itemname2 VARCHAR(20) NOT NULL,
  nums INT,
  id INT NOT NULL );
 
INSERT INTO public.identity_test (itemname1, itemname2, nums, id)
SELECT
  public.sourcetable.itemname1,
  public.sourcetable.itemname2,
  public.sourcetable.nums,
  ROW_NUMBER() OVER(ORDER BY
    public.sourcetable.itemname1,
    public.sourcetable.itemname2,
    public.sourcetable.nums)
  +
  CASE
    WHEN (SELECT MAX(id) FROM public.identity_test) IS NULL THEN 0
    ELSE (SELECT MAX(id) FROM public.identity_test)
  END AS id
FROM
  public.sourcetable
ORDER BY
  public.sourcetable.itemname1,
  public.sourcetable.itemname2,
  public.sourcetable.nums;

Amazon Redshiftの様な分散クラスタ環境では、利用者が想定しない形の機能の挙動があるというお話しでした。今回遭遇したケースでも、採番の数値飛び具合は若干あったものの、今後のデータ増分ペースを考慮してINT型の上限を超えることは無いだろう、という判断でIDENTITYでの採番を採用するに至りました。この辺り、上手く内容を見極めて活用して行きたいですね。こちらからは以上です。